<?php
namespace app\admin\model;
use think\Model;
use think\Db;
use app\admin\model\Exam as examModel;
class TestingLog extends Model
{
    function getTestingLog($id){
        if($id>0){
            $res = Db::name('testing_log')->json(['answer'])->find($id);
            $res['useTime'] = gmdate('H:i:s',$res['use_time']);
            return $res;
        }
    }
    //计算试卷分数
    function countScore($id){
        if($id>0){
            $testinglog = Db::name('testing_log')->json(['answer'])->find($id);
            $exam = Db::name('exam')->find($testinglog['examid']);
            $exam_setting = json_decode($exam['exam_setting'],true);
            $questionList = examModel::putSetting($exam_setting);
            $total_score = 0; //总分
            foreach($testinglog['answer'] as $k=>$v){
                $question = $questionList[$v['id']];
                $answer[$k]['is_right'] = 0;
                if($question['type']==1){
                    if($v['daan'] == $question['question_answer']){
                        $total_score = $total_score + $exam_setting['radio']['score']; //比较答案正确累计加分
//                        $answer[$k]['is_right'] = 1;
                    }
                }elseif($question['type']==2){
                    if($v['daan'] == $question['question_answer']){
                        $total_score = $total_score + $exam_setting['judge']['score']; //比较答案正确累计加分
//                        $answer[$k]['is_right'] = 1;
                    }
                }elseif($question['type']==3){
                    if($v['daan'] == $question['question_answer']){
                        $total_score = $total_score + $exam_setting['check']['score']; //比较答案正确累计加分
//                        $answer[$k]['is_right'] = 1;
                    }
                }elseif($question['type']==4){
                    if($v['is_right'] == 1){
                        $total_score = $total_score + $exam_setting['fill']['score']; //比较答案正确累计加分
//                        $answer[$k]['is_right'] = 1;
                    }
                }
            }
            $data['score'] = $total_score;
            $res = Db::name('testing_log')->where('id',$id)->update($data);
            if($res !== false){
                return true;
            }else{
                return false;
            }
        }
    }
    function testingLogAllList($getData){
        $where = [];
        if($getData['art_type']){
            $where[] = ['log.art_type','=',$getData['art_type']];
        }
        if($getData['status']){
            $where[] = ['log.status','=',$getData['status']];
        }
        if($getData['testingid']){
            $where[] = ['log.testingid','=',$getData['testingid']];
        }
        if($getData['subjectid']){
            $where[] = ['log.subjectid','=',$getData['subjectid']];
        }
        if($getData['examid']){
            $where[] = ['log.examid','=',$getData['examid']];
        }
        if($getData['user_name']){
            $user = Db::name('user')->where("user_name",$getData['user_name'])->find();
            if($user){
                $where[] = ['log.userid','=',$user['userid']];
            }

        }
//        dump($where);exit;
        $list = Db::name('testing_log')
            ->alias('log')
            ->join('testing t','t.testingid = log.testingid','left')
            ->join('subject s','s.subjectid = log.subjectid','left')
            ->join('exam e','e.examid = log.examid','left')
            ->join('user u','u.userid = log.userid','left')
            ->field('log.*,t.testing_name,s.subject_name,e.exam_name,u.user_name')
            ->where($where)
            ->select();

        return $list;

    }
    function testingLogData($size=15,$where=[]){
        $map = [];
//        if(input('keyword') && input('keyword')<>''){
//            $map[] = ['e.testing_name','like','%'.input('keyword').'%'];
//        }
        if($where <> ''){
            $map = array_merge($where,$map);
        }
//        dump($map);exit;
        $count = Db::name('testing_log')->alias('log')->where($map)->count();// 查询满足要求的总记录数
        $list = Db::name('testing_log')
            ->alias('log')
            ->join('testing t','t.testingid = log.testingid','left')
            ->join('subject s','s.subjectid = log.subjectid','left')
            ->join('exam e','e.examid = log.examid','left')
            ->join('user u','u.userid = log.userid','left')
            ->field('log.*,t.testing_name,s.subject_name,e.exam_name,u.user_name')
            ->where($map)
            ->order('id desc')
            ->paginate($size,$count,['query'=>request()->param()])
            ->each(function($item,$key){
                 $item['useTime'] = gmdate('H:i:s',$item['use_time']);
                return $item;
            });
        $page = $list->render();

        $data['count'] = $count;
        $data['list'] = $list;
        $data['page'] = $page;
        return $data;
    }

    function condition($data){
        $condition=[];
        if($data['art_type'] <> ''){
            $condition[] = ['log.art_type','=',$data['art_type']];
        }
        if($data['testingid'] <> ''){
            $condition[] = ['log.testingid','=',$data['testingid']];
        }
        if($data['subjectid'] <> ''){
            $condition[] = ['log.subjectid','=',$data['subjectid']];
        }
        if($data['examid'] <> ''){
            $condition[] = ['log.examid','=',$data['examid']];
        }
        if($data['status']==1 || $data['status']===0 || $data['status']==='0'){
            $condition[] = ['log.status','=',"{$data['status']}"];
        }
//        dump($data);
//        dump($condition);exit;
        if($data['user_name'] <> ''){
            //根据学号查找
            $user = Db::name('user')->where("user_name",$data['user_name'])->find();
            if($user){
                $condition[] = ['log.userid','=',$user['userid']];
            }else{
                $condition[] = ['log.userid','=',-1];
            }
        }

        return $condition;
    }

    //统计某期学员总分排名
    function testingRank($testingid,$art_type=1,$p=1){
        $subjectModel = new Subject();
//        $sql = "select log.userid,log.score from tp_testing_log log where log.subjectid=1";
//        $res = Db::query($sql);
//        $sql = "select log.userid,sum(log.score) sumscore from tp_testing_log log where log.testingid=20 group by log.userid";
//        $res1 = Db::query($sql);
        //某期入学检测
        $testing = Db::name("testing")->json(['testing_set'])->find($testingid);
        $num=20;
        $p = empty($p)?1:$p;
        $start = ($p-1)*$num;
//        $testing_set = $testing['testing_set'];
        //成绩联合查询
        //1文科生,2理科生 （subjectid 1语文，2数学，3英语，6地理，7历史）（subjectid 1语文，2数学，3英语，4物理，5化学）
        if($art_type==1){
            $sql = "
                select u.userid,u.user_name,c.yuwen,c.shuxue,c.yingyu,c.dili,c.lishi,c.total_score from
                (
                select s1.userid,s1.score yuwen,s2.score shuxue,s3.score yingyu,s6.score dili,s7.score lishi,b.sumscore total_score from
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=1) s1
                left JOIN
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=2) s2
                on s2.userid = s1.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=3) s3
                on s3.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=6) s6
                on s6.userid = s3.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=7) s7
                on s7.userid = s6.userid left join
                (select log.userid,sum(log.score) sumscore from tp_testing_log log where (status=1 and testingid={$testingid}) and art_type=1 and (log.subjectid in (1,2,3,6,7)) group by log.userid) b
                on s7.userid = b.userid
                ) c
                left join tp_user u on u.userid=c.userid order by c.total_score desc limit {$start},{$num}
          ";
            $sql_count = "
                select count(*) logCount from
                (
                select s1.userid,s1.score yuwen,s2.score shuxue,s3.score yingyu,s6.score dili,s7.score lishi,b.sumscore total_score from
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=1) s1
                left JOIN
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=2) s2
                on s1.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=3) s3
                on s3.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=6) s6
                on s6.userid = s3.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=7) s7
                on s7.userid = s6.userid left join
                (select log.userid,sum(log.score) sumscore from tp_testing_log log where (status=1 and testingid={$testingid}) and art_type=1 and (log.subjectid in (1,2,3,6,7)) group by log.userid) b
                on s7.userid = b.userid
                ) c
                left join tp_user u on u.userid=c.userid order by c.total_score desc
          ";
        }elseif($art_type==2){
            $sql = "
                select u.userid,u.user_name,c.yuwen,c.shuxue,c.yingyu,c.wuli,c.huaxue,c.total_score from
                (
                select s1.userid,s1.score yuwen,s2.score shuxue,s3.score yingyu,s4.score wuli,s5.score huaxue,b.sumscore total_score from
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=1) s1
                left JOIN
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=2) s2
                on s2.userid = s1.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=3) s3
                on s3.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=4) s4
                on s4.userid = s3.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=5) s5
                on s5.userid = s4.userid left join
                (select log.userid,sum(log.score) sumscore from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and (log.subjectid in (1,2,3,4,5)) group by log.userid) b
                on s5.userid = b.userid
                ) c
                left join tp_user u on u.userid=c.userid order by c.total_score desc limit {$start},{$num}
          ";
            $sql_count = "
                select count(*) logCount from
                (
                select s1.userid,s1.score yuwen,s2.score shuxue,s3.score yingyu,s4.score wuli,s5.score huaxue,b.sumscore total_score from
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=1) s1
                left JOIN
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=2) s2
                on s2.userid = s1.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=3) s3
                on s3.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=4) s4
                on s4.userid = s3.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=5) s5
                on s5.userid = s4.userid left join
                (select log.userid,sum(log.score) sumscore from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and (log.subjectid in (1,2,3,4,5)) group by log.userid) b
                on s5.userid = b.userid
                ) c
                left join tp_user u on u.userid=c.userid order by c.total_score desc
          ";
        }else{
            exitjson(0,'参数错误testingRank');
        }
//        dump($sql);exit;
        $data['list'] = Db::query($sql);
        $data['count'] = Db::query($sql_count);

        return $data;

    }

    //统计某期学员总分排名
    function testingRankAllList($testingid,$art_type=1){
        //成绩联合查询
        //1文科生,2理科生 （subjectid 1语文，2数学，3英语，6地理，7历史）（subjectid 1语文，2数学，3英语，4物理，5化学）
        if($art_type==1){
            $sql = "
                select u.userid,u.user_name,c.yuwen,c.shuxue,c.yingyu,c.dili,c.lishi,c.total_score from
                (
                select s1.userid,s1.score yuwen,s2.score shuxue,s3.score yingyu,s6.score dili,s7.score lishi,b.sumscore total_score from
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=1) s1
                left JOIN
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=2) s2
                on s1.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=3) s3
                on s3.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=6) s6
                on s6.userid = s3.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=1 and log.subjectid=7) s7
                on s7.userid = s6.userid left join
                (select log.userid,sum(log.score) sumscore from tp_testing_log log where (status=1 and testingid={$testingid}) and art_type=1 and (log.subjectid in (1,2,3,6,7)) group by log.userid) b
                on s7.userid = b.userid
                ) c
                left join tp_user u on u.userid=c.userid order by c.total_score desc
          ";
        }elseif($art_type==2){
            $sql = "
                select u.userid,u.user_name,c.yuwen,c.shuxue,c.yingyu,c.wuli,c.huaxue,c.total_score from
                (
                select s1.userid,s1.score yuwen,s2.score shuxue,s3.score yingyu,s4.score wuli,s5.score huaxue,b.sumscore total_score from
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=1) s1
                left JOIN
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=2) s2
                on s1.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=3) s3
                on s3.userid = s2.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=4) s4
                on s4.userid = s3.userid left join
                (select log.userid,log.score from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and log.subjectid=5) s5
                on s5.userid = s4.userid left join
                (select log.userid,sum(log.score) sumscore from tp_testing_log log where status=1 and testingid={$testingid} and art_type=2 and (log.subjectid in (1,2,3,4,5)) group by log.userid) b
                on s5.userid = b.userid
                ) c
                left join tp_user u on u.userid=c.userid order by c.total_score desc
          ";
        }else{
            exitjson(0,'参数错误testingRankAllList');
        }
       // dump($sql);exit;
        $res = Db::query($sql);

        return $res;
    }


}